DDL permissions should be granted only to authorized accounts.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2463	DM1760-SQLServer9	SV-23804r2_rule	ECLP-1	Medium

Description
Data Definition Language (DDL) commands include CREATE, ALTER, and DROP object actions. These actions cause changes to the structure, definition and configuration of the DBMS as well as to the objects themselves that can affect any or all operations of the database. Such privileged actions, when not restricted to authorized persons and activities, can lead to a compromise of data and DBMS availability.

Description

Data Definition Language (DDL) commands include CREATE, ALTER, and DROP object actions. These actions cause changes to the structure, definition and configuration of the DBMS as well as to the objects themselves that can affect any or all operations of the database. Such privileged actions, when not restricted to authorized persons and activities, can lead to a compromise of data and DBMS availability.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22835r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8) LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3 WHERE p.state_desc <> 'DENY' AND p.state IN ('G', 'W') AND (p.type LIKE 'CR%' OR p.type LIKE 'AL%') ORDER BY u.name, o.name, p.permission_name Compare the results listed to those documented in the System Security Plan. If any accounts listed are: 1. Application users 2. Application user roles 3. Application administrator roles 4. PUBLIC role 5. GUEST user this is a Finding. If any application developer accounts are listed with DDL privileges to production databases, this is a Finding. If results are not documented in the System Security Plan or authorized by the IAO, this is a Finding.

Check Text ( C-22835r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> 'DENY'
AND p.state IN ('G', 'W')
AND (p.type LIKE 'CR%' OR p.type LIKE 'AL%')
ORDER BY u.name, o.name, p.permission_name

Compare the results listed to those documented in the System Security Plan. If any accounts listed are:

1. Application users
2. Application user roles
3. Application administrator roles
4. PUBLIC role
5. GUEST user

this is a Finding.

If any application developer accounts are listed with DDL privileges to production databases, this is a Finding.

If results are not documented in the System Security Plan or authorized by the IAO, this is a Finding.

Fix Text (F-14854r1_fix)
Revoke DDL permissions from unauthorized accounts with the REVOKE command: From the query prompt: USE [database name] REVOKE [permission] FROM [user name] Document required DDL permission grants in the System Security Plan and authorize with the IAO.